-- @author lixiyong01
-- @date 2023.03.14
-- 健康分汇总-存储/任务

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score_appid partition (dt=${dateSuffix})
select tag, project, team, owner_id, app_id, app_name,
    avg(weight) as weight,
    greatest(100-sum(100-score),0) as health_score,
    nvl(concat_ws(',',collect_list(if(score=100,null,concat(code,':',reason,':',100-score)))),'-') as health_reason,
    concat_ws(',',collect_list(data_content)) as data_content
from (
    -- 可能会有多个规则扣分，仅仅取扣分最多的
    select tag, code, project, team, owner_id, app_id, app_name, weight, data_content, score, reason
    from (
        select cast(its[0] as int) as score, its[1] as reason,
            tag, code, project, team, owner_id, app_id, app_name, weight, data_content,
            row_number () over (partition by  tag,app_id,app_name,owner_id order by cast(its[0] as int) asc)  rn
        from (
    	    select split(health_score,',') as its,
                tag, code, project, team, owner_id, app_id, app_name, weight,
                data_content
            from hdp_lbg_supin_zplisting.app_zp_rock_health_score
            where dt='${#date(0, 0, -1):yyyyMMdd#}'
    	) t0
    )t1
    -- where rn=1
) m
group by tag, project, team, owner_id,  app_id, app_name;

